package com.example.stringbufferlogger.Utils;

import com.example.stringbufferlogger.entity.FileConfig;
import com.example.stringbufferlogger.entity.FileDataRecord;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.stringbufferlogger.mapper.FileConfigMapper;
import com.example.stringbufferlogger.mapper.InterfaceMappingConfigMapper;
import com.example.stringbufferlogger.service.FileConfigService;
import com.example.stringbufferlogger.service.ReplayLogService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.example.stringbufferlogger.Utils.OSSUtil.getFileFromOSS;

@Component
public class ExcelUtils {

    @Autowired(required = false)
    private IService<FileDataRecord> fileDataRecordService;

    @Autowired
    private FileConfigService fileConfigService;
    @Autowired
    private ReplayLogService replayLogService;
    @Autowired
    private FileConfigMapper fileConfigMapper;
    @Autowired
    InterfaceMappingConfigMapper interfaceMappingConfigMapper;


    /**
     * 处理Excel文件并保存数据到数据库
     * @param inputStream Excel文件输入流
     * @param interfaceUrl 接口URL
     * @param requestId 请求ID
     * @param recordType 记录类型(新/老)
     */
    @Transactional
    public void processExcel(InputStream inputStream, String interfaceUrl, String requestId, String recordType) {
        try {
            // 1. 读取Excel文件
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            // 获取该接口对应的文件配置
            FileConfig config = (FileConfig) fileConfigService.getFileConfigsByInterfaceAdrs(interfaceUrl);
            if (config == null) {
                throw new RuntimeException("未找到接口URL对应的配置: " + interfaceUrl);
            }

            // 获取需要映射的字段列表（对应file_field配置）
            String mappingFieldStr = config.getMappingField();
            List<String> mappingFields = new ArrayList<>();
            if (mappingFieldStr != null && !mappingFieldStr.isEmpty()) {
                // 假设 mappingFieldStr 是用逗号分隔的多个字段
                String[] fields = mappingFieldStr.split(",");
                for (String field : fields) {
                    String trimmedField = field.trim();
                    if (!trimmedField.isEmpty()) {
                        mappingFields.add(trimmedField);
                    }
                }
            }
            if (mappingFields == null || mappingFields.isEmpty()) {
                throw new RuntimeException("配置中未设置映射字段");
            }

            // 构建Excel表头与列索引的映射
            Row headerRow = sheet.getRow(0);
            Map<String, Integer> columnNameToIndexMap = new HashMap<>();
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                if (cell != null) {
                    columnNameToIndexMap.put(cell.getStringCellValue(), i);
                }
            }

            // 用于存储所有行参与计算的拼接字符串
            StringBuffer allRowsCompareValueBuffer = new StringBuffer();

            // 2. 处理数据行
            List<FileDataRecord> records = new ArrayList<>();
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) continue;

                FileDataRecord record = new FileDataRecord();
                record.setRecordType(recordType);
                record.setInterfaceUrl(interfaceUrl);
                record.setRequestId(requestId);
                record.setProcessTime(new Timestamp(System.currentTimeMillis()));

                StringBuffer rowCompareValueBuffer = new StringBuffer(); // 当前行用于MD5计算的拼接值
                StringBuffer originalDataBuffer = new StringBuffer(); // 原始数据记录

                // 按配置的字段顺序处理每个字段
                for (int i = 0; i < mappingFields.size() && i < 50; i++) { // 最多处理50个字段
                    String fieldName = mappingFields.get(i);
                    Integer colIndex = columnNameToIndexMap.get(fieldName);

                    if (colIndex == null) {
                        record.setErrorMsg("Excel中缺少配置的字段: " + fieldName);
                        continue;
                    }

                    Cell cell = row.getCell(colIndex);
                    String value = getCellValueAsString(cell);

                    // 记录原始数据（字段名:值;）
                    originalDataBuffer.append(value);

                    // 使用反射设置字段值
                    try {
                        String setterName = "setMappingField" + (i + 1);
                        FileDataRecord.class.getMethod(setterName, String.class).invoke(record, value);

                        // 如果isIgnore为0，则加入MD5计算
                        if (config.getIsIgnore() != null && config.getIsIgnore() == 0) {
                            rowCompareValueBuffer.append(value);
                            allRowsCompareValueBuffer.append(value);
                        }
                    } catch (Exception e) {
                        record.setErrorMsg("设置字段值时出错: " + e.getMessage());
                    }
                }

                // 保存原始数据
                record.setOriginalData(originalDataBuffer.toString());
                records.add(record);
            }

            // 计算所有行参与拼接后的MD5值
            String allRowsMd5 = "";
            if (allRowsCompareValueBuffer.length() > 0) {
                allRowsMd5 = new MD5Utils().getMD5(allRowsCompareValueBuffer.toString());
            }

            // 为所有记录设置相同的MD5值
            for (FileDataRecord record : records) {
                record.setMd5Code(allRowsMd5);
            }

            // 3. 批量保存数据
            if (fileDataRecordService != null) {
                fileDataRecordService.saveBatch(records);
            }

            workbook.close();
        } catch (Exception e) {
            throw new RuntimeException("处理Excel文件失败", e);
        }
    }

    /**
     * 处理Excel文件并保存数据到数据库
     * @param requestId 请求ID
     */
    @Transactional
    public void processOssExcel( String requestId) {
        try {
            String targetDir = System.getProperty("java.io.tmpdir") ;

            getBothVersions(targetDir,requestId);
            String oldPath = replayLogService.selectPathByRequestIdAndStatus(requestId);
            String newPath = interfaceMappingConfigMapper.getNewPath(oldPath);

            // 处理 new 文件夹中的文件
            processSingleFolder(targetDir + "\\new", newPath, requestId, "new");
            // 处理 old 文件夹中的文件
            processSingleFolder(targetDir + "\\old", oldPath, requestId, "old");

        } catch (Exception e) {
            throw new RuntimeException("处理Excel文件失败", e);
        }
    }

    /**
     * 处理单个文件夹中的 Excel 文件
     * @param folderPath 文件夹路径
     * @param interfaceUrl 接口URL
     * @param requestId 请求ID
     * @param recordType 记录类型(新/老)
     */
    private void processSingleFolder(String folderPath, String interfaceUrl, String requestId, String recordType) throws Exception {
        if(recordType.equals("old")){
            interfaceUrl = interfaceMappingConfigMapper.getNewPath(interfaceUrl);
        }

        File folder = new File(folderPath);
        if (folder.exists() && folder.isDirectory()) {
            File[] files = folder.listFiles();
            if (files != null) {
                for (File file : files) {
                    if (file.isFile() && file.getName().endsWith(".xlsx")) {
                        try (Workbook workbook = new XSSFWorkbook(file)) {
                            Sheet sheet = workbook.getSheetAt(0);

                            // 1. 获取接口对应的配置列表
                            List<FileConfig> configList = fileConfigService.getFileConfigsByInterfaceAdrs(interfaceUrl);

                            // 2. 构建Excel表头映射(列名->列索引)
                            Row headerRow = sheet.getRow(0);
                            Map<String, Integer> columnNameToIndexMap = new HashMap<>();
                            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                                Cell cell = headerRow.getCell(i);
                                if (cell != null) {
                                    columnNameToIndexMap.put(cell.getStringCellValue(), i);
                                }
                            }

                            List<FileDataRecord> records = new ArrayList<>();

                            // 3. 按行处理数据
                            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                                Row row = sheet.getRow(rowNum);
                                if (row == null) continue;

                                FileDataRecord record = new FileDataRecord();
                                record.setRecordType(recordType);
                                record.setInterfaceUrl(interfaceUrl);
                                record.setRequestId(requestId);
                                record.setProcessTime(new Timestamp(System.currentTimeMillis()));

                                StringBuilder rowCompareValueBuffer = new StringBuilder(); // 当前行用于MD5计算的拼接值
                                StringBuilder originalDataBuffer = new StringBuilder(); // 原始数据记录

                                // 按配置的字段顺序处理每个字段
                                for (FileConfig config : configList) {
                                    String excelColumnName = config.getFileField();
                                    String mappingField = config.getMappingField();
                                    Integer colIndex = columnNameToIndexMap.get(excelColumnName);

                                    if (colIndex == null) {
                                        record.setErrorMsg((record.getErrorMsg() == null ? "" : record.getErrorMsg() + "; ") + "Excel中缺少配置的字段: " + excelColumnName);
                                        continue;
                                    }

                                    Cell cell = row.getCell(colIndex);
                                    String value = getCellValueAsString(cell);

                                    // 记录原始数据（字段名:值;）
                                    originalDataBuffer.append(excelColumnName).append(":").append(value).append(";");

                                    // 使用反射设置字段值
                                    try {
                                        String setterName = "set" + mappingField.substring(0, 1).toUpperCase() + mappingField.substring(1);
                                        FileDataRecord.class.getMethod(setterName, String.class).invoke(record, value);

                                        // 如果isIgnore为0，则加入MD5计算
                                        if (config.getIsIgnore() != null && config.getIsIgnore() == 0) {
                                            rowCompareValueBuffer.append(value);
                                        }
                                    } catch (Exception e) {
                                        record.setErrorMsg((record.getErrorMsg() == null ? "" : record.getErrorMsg() + "; ") + "设置字段值时出错: " + e.getMessage());
                                    }
                                }

                                // 保存原始数据
                                record.setOriginalData(originalDataBuffer.toString());

                                // 计算当前行的MD5值
                                String rowMd5 = rowCompareValueBuffer.length() > 0
                                        ? MD5Utils.getMD5(rowCompareValueBuffer.toString())
                                        : "";
                                record.setMd5Code(rowMd5);

                                records.add(record);
                            }

                            // 批量保存数据
                            if (fileDataRecordService != null) {
                                fileDataRecordService.saveBatch(records);
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * 将单元格值转换为字符串(通用方法)
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }

    public void getBothVersions(String path ,String reqId) {

        String fileName = reqId+fileConfigMapper.selectFileTypeByRequestId(reqId);

        String basePath =path;
        // 获取new目录下的文件并保存本地
        getFileFromOSS(basePath+"/new",fileName,"new");

        // 获取old目录下的文件并保存本地
        getFileFromOSS(basePath+"/old",fileName,"old" );

    }
}